package com.ccjq.jq173.shopping.dao.impl;

import com.ccjq.jq173.core.util.DbConnection;
import com.ccjq.jq173.product.po.Product;
import com.ccjq.jq173.shopping.dao.ShoppingCartDao;
import com.ccjq.jq173.shopping.po.ShoppingCart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ShoppingCartDaoImpl implements ShoppingCartDao {
    private Product product;
    private List list;
    @Override
    public List<ShoppingCart> queryByUserId(String userId) {
        String sql="select SHOPPING_CART_ID,PRODUCT_ID,QUANTITY,USER_Id from T_SHOPPING_CART where user_id=? ";
        ShoppingCart u=null;
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        list =new ArrayList(  );
        try
        {
            conn= DbConnection.getConnection();
            st=conn.prepareStatement(sql);

            st.setString(1,userId);
            rs=st.executeQuery();
            while(rs.next()){
                u =new ShoppingCart();
                u.setShoppingCarId( rs.getString( 1 ) );
                u.setProductId( rs.getString( 2 ) );
                u.setQuantity( rs.getInt( 3 ) );
                u.setUserId( rs.getString( 4 ) );
                u.setProduct( p(rs.getString( 2 )));
                System.out.println( u.getQuantity()*Integer.parseInt(  u.getProduct().getPrice()) );
                u.setSubtotal( u.getQuantity()*Integer.parseInt(  u.getProduct().getPrice()) );
                list.add( u );
            }

        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DbConnection.close(rs,st,conn);
        }
        return list;
    }
    public Product p(String product_id)
    {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        String sql="select a.product_id,description,price,model,feature,imgpath,novalty_status,publish_date,c.quantity,a.category_id,b.name from t_product a join t_category b on a.category_id=b.category_id  join t_stock c on a.product_id=c.product_id where a.product_id=?";
        Product p=null;
        conn=DbConnection.getConnection();
        try {
            conn=DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            st.setString( 1,product_id );
            rs=st.executeQuery();
            if(rs.next())
            {
                p=new Product();
                p.setProductId(rs.getString(1));
                p.setDescription(rs.getString(2));
                p.setPrice(rs.getString(3));
                p.setModel(rs.getString(4));
                p.setFeature(rs.getString(5));
                p.setImgpath(rs.getString(6));
                p.setNovaltyStatus(rs.getInt(7));
                p.setPublishDate(rs.getDate(8));
                p.setQuantity(rs.getInt(9));
                p.setCategoryId(rs.getString(10));
                p.setCategoryName(rs.getString(11));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DbConnection.close( rs,st,conn );
        }
        return p;
    }

    @Override
    public ShoppingCart queryByCartId(String shoppingCartId) {
        String sql = "select * from T_SHOPPING_CART where SHOPPING_CART_ID=?";
        ShoppingCart u=null;
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        try
        {
            conn= DbConnection.getConnection();
            st=conn.prepareStatement(sql);
            rs=st.executeQuery();
            u.setShoppingCarId( rs.getString( 1 ) );
            u.setProductId( rs.getString( 2 ) );
            u.setQuantity(  rs.getInt( 3 ));
            u.setUserId( rs.getString( 4 ) );
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DbConnection.close(rs,st,conn);
        }
        return u;
    }

    @Override
    public int saveShoppingCart(int quantity, String productId, String userId) {
        String sql1= "update T_SHOPPING_CART set quantity=? where product_id=? and user_Id=?";
        Connection conn=null;
        PreparedStatement st=null;
        int result=0;
        ResultSet rs=null;
        int result1=0;
        try
        {
            conn= DbConnection.getConnection();
            st=conn.prepareStatement(sql1);

            st.setInt( 1,quantity );
            st.setString( 2,productId );
            st.setString( 3,userId );
            result1 = st.executeUpdate();
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DbConnection.close(rs,st,conn);
        }
        return result1;
    }

    @Override
    public int updatShoppingCart(String shoppingCartId, String productId, String userId) {
        String sql ="delete from T_SHOPPING_CART where SHOPPING_CART_ID=? and product_Id=? and user_Id=?";
        Connection conn=null;
        PreparedStatement st=null;
        int result1=0;
        try
        {
            conn= DbConnection.getConnection();
            st = conn.prepareStatement( sql );

            st.setString( 1,shoppingCartId );
            st.setString( 2,productId );
            st.setString( 3, userId);
            result1=st.executeUpdate();
            System.out.println( result1 );
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DbConnection.close(null,st,conn);
        }
        return result1;
    }

    @Override
    public int updateStock(String productId, int quantity) {
        String sql ="update T_STOCK set quantity=(select quantity from t_stock where product_id=?)-? where product_id=?";
        Connection conn=null;
        PreparedStatement st=null;
        int result = 0;
        try {
            conn= DbConnection.getConnection();
            st = conn.prepareStatement( sql );
            st.setString( 1,productId );
            st.setInt( 2,quantity );
            st.setString( 3,productId );
            result=st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close( null,st,conn );
        }
        return result;
    }

    @Override
    public void updateT_ORDER_PRODUCT(String orderId, List<ShoppingCart> list) {
        String sql ="insert into T_ORDER_PRODUCT(ORDER_ID,PRODUCT_ID,QUANTITY,price)"+
                "values(?,?,?,?)";
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        try {
            conn= DbConnection.getConnection();
            st =conn.prepareStatement( sql );

            for(Object o : list) {
                st.setString( 1,orderId);
                ShoppingCart shoppingCart =(ShoppingCart)o;
                st.setString( 2,shoppingCart.getProductId() );
                st.setInt( 3, shoppingCart.getQuantity());
                st.setObject( 4,shoppingCart.getProduct().getPrice() );
                rs = st.executeQuery();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(null,st,conn);
        }
    }

    @Override
    public int saveShoppingCart(int quantity, String shoppingCarId, String userId, String productId) {
        String sql ="insert into T_SHOPPING_CART(SHOPPING_CART_ID,PRODUCT_ID,QUANTITY,USER_ID)"+
                "values(?,?,?,?)";
        Connection conn=null;
        String sql1 = "select quantity from T_SHOPPING_CART where product_Id=?";
        PreparedStatement st=null;
        ResultSet rs=null;
        int result=0;
        try {
            conn= DbConnection.getConnection();
            st =conn.prepareStatement( sql1 );
            st.setString( 1, productId);
            rs = st.executeQuery();
            if(rs.next()){
                result = new ShoppingCartDaoImpl().saveShoppingCart( rs.getInt( 1 )+1,productId,userId );

            }else {
                st = conn.prepareStatement( sql );
                st.setString( 1, shoppingCarId );
                st.setString( 2, productId );
                st.setInt( 3, quantity );
                st.setString( 4, userId );
                result=st.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close(null,st,conn);
        }
        return  result;
    }

    /**
     * 结算模板方法
     * @param params 参数列表
     * @return 成功1 失败0
     */
    @Override
    public int settleAccounts(Map<String, Object> params) {
        String sql ="insert into t_order(order_Id, create_time, payment, amount, receiver, province, city, county," +
                " address, zipcode, telphone, user_Id) values(?,sysdate,?,?,?,?,?,?,?,?,?,?)";
        Connection conn=null;
        PreparedStatement st=null;
        int result = 0;
        try {
            conn = DbConnection.getConnection();
            st = conn.prepareStatement( sql );
            st.setObject( 1,params.get( "orderId" ) );
            st.setInt( 2,(int)params.get( "payment" ) );
            st.setDouble( 3,(double)params.get( "amount" ) );
            st.setObject( 4,params.get( "receiver" ) );
            st.setObject( 5,params.get( "province" ) );
            st.setObject( 6,params.get( "city" ) );
            st.setObject( 7,params.get( "county" ) );
            st.setObject( 8,params.get( "address" ) );
            st.setObject( 9,params.get( "zipcode" ) );
            st.setObject( 10,params.get( "telphone" ) );
            st.setObject( 11,params.get( "userId" ) );
            result =st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DbConnection.close( null,st,conn );
        }
        return result;
    }
}
